﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using FarPoint.Web.Spread;
using System.Data;
using FarPoint.Web.Spread.Model;

namespace ControlExplorer
{
    public partial class FixedForm : System.Web.UI.Page
    {
        #region 事件处理

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                // 模板编号
                string templateid = null;
                templateid = Request.QueryString["tid"];
                hfTemplateID.Value = templateid;

                // 历史编号
                string historyid = null;
                historyid = Request.QueryString["hid"];
                hfHistoryID.Value = historyid;

                // 填报时间
                txtDate.Text = DateTime.Today.ToShortDateString();

                // 更新菜单状态
                UpdateMenuState("New");

                // 初始化模板
                InitializeSheetView();

                // 填充历史数据
                if (!string.IsNullOrWhiteSpace(historyid))
                {
                    InitializeHistoryDetail(historyid);
                }
            }
        }

        #endregion

        #region 菜单操作

        // 新建数据
        protected void btnNew_Click(object sender, EventArgs e)
        {
            hfHistoryID.Value = null;

            UpdateMenuState("New");

            InitializeSheetView();
        }

        // 编辑数据
        protected void btnEdit_Click(object sender, EventArgs e)
        {
            UpdateMenuState("Edit");

            FpSpread1.Sheets[0].OperationMode = OperationMode.Normal;
        }

        // 保存数据
        protected void btnSave_Click(object sender, EventArgs e)
        {
            #region 将用户录入的数据保存到数据库中

            FpSpread1.SaveChanges();

            string historyid = "";

            historyid = SaveHistory(hfTemplateID.Value);

            SaveData(historyid, hfTemplateID.Value);

            UpdateMenuState("View");

            hfHistoryID.Value = historyid;

            FpSpread1.Sheets[0].OperationMode = OperationMode.SingleSelect;

            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "save", "alert('数据保存成功！');", true);

            #endregion
        }

        // 获取历史填报数据
        protected void btnHistory_Click(object sender, EventArgs e)
        {
            UpdateMenuState("History");

            InitializeHistoryList();

            #region ACCESS
            string commandstring = string.Format("SELECT TOP 30 * FROM 填报记录 WHERE 模板编号 = '{0}' ORDER BY 填报时间 DESC ", hfTemplateID.Value);
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                FpSpread1.Sheets[0].DataSource = dt;

                int columncount = FpSpread1.Sheets[0].ColumnCount - 1;

                for (int i = 0; i < FpSpread1.Sheets[0].RowCount; i++)
                {
                    FarPoint.Web.Spread.ButtonCellType btn = new FarPoint.Web.Spread.ButtonCellType("HistoryDetail");
                    FarPoint.Web.Spread.StyleInfo style = new FarPoint.Web.Spread.StyleInfo();
                    btn.ButtonType = FarPoint.Web.Spread.ButtonType.LinkButton;
                    btn.Text = "查看";
                    btn.CommandName = "HistoryDetail";
                    style.CellType = btn;
                    FpSpread1.Sheets[0].Cells[i, columncount].CellType = btn;
                }
            }

            #endregion

        }

        #endregion

        #region Spread 操作

        protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
        {
            FpSpread1.SaveChanges();
            if (e.CommandName == "HistoryDetail")
            {
                string historyid = FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].ActiveRow, 0].Text;

                hfHistoryID.Value = historyid;

                InitializeSheetView();

                if (!string.IsNullOrWhiteSpace(historyid))
                {
                    InitializeHistoryDetail(historyid);
                }
            }
        }

        #endregion

        #region 私有方法

        // 初始化模板
        private void InitializeSheetView()
        {
            SheetSkin skin = FpSpread1.Sheets[0].ActiveSkin;

            FpSpread1.Reset();
            FpSpread1.EditModePermanent = true;
            FpSpread1.Sheets.Clear();
            FpSpread1.Sheets.Add(new SheetView());
            FpSpread1.UseClipboard = false;
            FpSpread1.ClientAutoCalculation = true;
            FpSpread1.Open(DataTools.GetTemplateStream(hfTemplateID.Value));
            FpSpread1.Sheets[0].AllowPage = false;
            FpSpread1.Sheets[0].AutoCalculation = true;
            FpSpread1.Sheets[0].Protect = true;

            FpSpread1.Sheets[0].ActiveSkin = skin;
            FpSpread1.Sheets[0].LockBackColor = System.Drawing.Color.LightGray;
            lTitle.Text = DataTools.GetTemplate(hfTemplateID.Value)["模板名称"].ToString();
        }

        // 初始化填报历史
        private void InitializeHistoryList()
        {
            Panel1.Visible = false;
            btnSave.Visible = false;
            SheetSkin skin = FpSpread1.Sheets[0].ActiveSkin;

            FpSpread1.Open(Server.MapPath("~\\Templates\\填报记录.xml"));
            FpSpread1.UseClipboard = false;
            FpSpread1.ClientAutoCalculation = true;
            FpSpread1.Sheets[0].AllowPage = true;
            FpSpread1.Sheets[0].PageSize = 20;
            FpSpread1.Sheets[0].AutoCalculation = true;
            FpSpread1.Sheets[0].Protect = true;

            FpSpread1.Sheets[0].ActiveSkin = skin;

            FpSpread1.Sheets[0].AutoGenerateColumns = false;
            FpSpread1.Sheets[0].DataAutoCellTypes = false;

            FpSpread1.Sheets[0].Columns[0].DataField = "记录编号";
            FpSpread1.Sheets[0].Columns[1].DataField = "模板编号";
            FpSpread1.Sheets[0].Columns[2].DataField = "模板名称";
            FpSpread1.Sheets[0].Columns[3].DataField = "部门";
            FpSpread1.Sheets[0].Columns[4].DataField = "填报人";
            FpSpread1.Sheets[0].Columns[5].DataField = "填报时间";
            FpSpread1.Sheets[0].Columns[6].DataField = "修改时间";

            // 添加过滤功能
            FpSpread1.Sheets[0].AutoFilterMode = FarPoint.Web.Spread.AutoFilterMode.Enhanced;
            FarPoint.Web.Spread.IRowFilter rowFilter = new FarPoint.Web.Spread.HideRowFilter(FpSpread1.Sheets[0]);
            FarPoint.Web.Spread.FilterColumnDefinition fd3 = new FarPoint.Web.Spread.FilterColumnDefinition(3, FarPoint.Web.Spread.FilterListBehavior.Default);
            FarPoint.Web.Spread.FilterColumnDefinition fd4 = new FarPoint.Web.Spread.FilterColumnDefinition(4, FarPoint.Web.Spread.FilterListBehavior.Default);
            FarPoint.Web.Spread.FilterColumnDefinition fd5 = new FarPoint.Web.Spread.FilterColumnDefinition(5, FarPoint.Web.Spread.FilterListBehavior.Default);
            FarPoint.Web.Spread.FilterColumnDefinition fd6 = new FarPoint.Web.Spread.FilterColumnDefinition(6, FarPoint.Web.Spread.FilterListBehavior.Default);

            rowFilter.ColumnDefinitions.Add(fd3);
            rowFilter.ColumnDefinitions.Add(fd4);
            rowFilter.ColumnDefinitions.Add(fd5);
            rowFilter.ColumnDefinitions.Add(fd6);

            FpSpread1.Sheets[0].RowFilter = rowFilter;
            FpSpread1.ButtonCommand += new SpreadCommandEventHandler(FpSpread1_ButtonCommand);
        }

        // 加载填报历史数据
        private void InitializeHistoryDetail(string historyid)
        {
            UpdateMenuState("View");
            FpSpread1.Sheets[0].AllowPage = false;
            FpSpread1.Sheets[0].OperationMode = OperationMode.SingleSelect;

            string tablename = DataTools.GetTemplateDataSource(hfTemplateID.Value);

            #region 数据操作
            string commandstring = string.Format("SELECT * FROM [{0}] WHERE 记录编号 = '{1}'", tablename, historyid);
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[dt.Rows.Count - 1];

                    string columnname = "";

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        columnname = dt.Columns[i].ColumnName;

                        FarPoint.CalcEngine.ExternalCellExpression expr = FpSpread1.Sheets[0].GetCustomName(columnname) as FarPoint.CalcEngine.ExternalCellExpression;
                        if (expr != null)
                        {
                            FpSpread1.Sheets[0].SetValue(expr.Row, expr.Column, dr[columnname]);
                        }
                    }
                }

            }

            #endregion

        }

        // 保存数据
        private void SaveData(string historyid, string templateid)
        {
            #region 数据操作

            string commandstring = "";
            string tablename = DataTools.GetTemplateDataSource(templateid);

            if (string.IsNullOrEmpty(hfHistoryID.Value))
            {
                commandstring = string.Format("SELECT * FROM [{0}]", tablename);
            }
            else
            {
                commandstring = string.Format("SELECT * FROM [{0}] WHERE 记录编号 = '{1}'", tablename, historyid);
            }

            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                DataRow dr = (string.IsNullOrWhiteSpace(hfHistoryID.Value) ? dt.NewRow() : dt.Rows[0]);
                string columnname = "";

                for (int i = 1; i < dt.Columns.Count; i++)
                {
                    columnname = dt.Columns[i].ColumnName;

                    FarPoint.CalcEngine.ExternalCellExpression expr = FpSpread1.Sheets[0].GetCustomName(columnname) as FarPoint.CalcEngine.ExternalCellExpression;
                    if (expr != null)
                    {
                        object value = FpSpread1.Sheets[0].GetValue(expr.Row, expr.Column);
                        dr[columnname] = (value == null ? System.DBNull.Value : value);
                    }
                }

                // 新建数据
                if (string.IsNullOrWhiteSpace(hfHistoryID.Value))
                {
                    dr["记录编号"] = historyid;
                    dt.Rows.Add(dr);
                }

                adapter.Update(dt);
            }

            #endregion
        }

        // 保存模板填报历史记录
        private string SaveHistory(string templateid)
        {
            string hisoryid = (string.IsNullOrWhiteSpace(hfHistoryID.Value) ? Guid.NewGuid().ToString() : hfHistoryID.Value);

            #region 数据操作

            string commandstring = "SELECT TOP 50 * FROM 填报记录 WHERE 记录编号 = '" + hisoryid + "'";
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                DataRow dr = (string.IsNullOrWhiteSpace(hfHistoryID.Value) ? dt.NewRow() : dt.Rows[0]);

                if (string.IsNullOrWhiteSpace(hfHistoryID.Value))
                {
                    // 新建数据
                    dr["记录编号"] = hisoryid;
                    dr["模板编号"] = templateid;
                    dr["部门"] = lstCategoryID.SelectedItem.Text;
                    dr["填报人"] = txtName.Text;
                    dr["填报时间"] = DateTime.Now;
                    dr["修改时间"] = DateTime.Now;

                    dt.Rows.Add(dr);
                }
                else
                {
                    // 历史数据
                    dr["修改时间"] = DateTime.Now;
                }

                adapter.Update(dt);
            }

            #endregion

            return hisoryid;
        }

        // 更新菜单状态
        private void UpdateMenuState(string action)
        {
            switch (action)
            {
                case "New":
                    Panel1.Visible = true;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = true;
                    btnHistory.Visible = true;
                    break;
                case "Edit":
                    Panel1.Visible = false;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = true;
                    btnHistory.Visible = true;
                    break;
                case "View":
                    Panel1.Visible = false;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = false;
                    btnHistory.Visible = true;
                    break;
                case "History":
                    Panel1.Visible = false;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = false;
                    btnHistory.Visible = false;
                    break;
                default:
                    break;
            }
        }

        #endregion
    }
}